package servlets;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class StaffDAO {
    
    private static final String CREATE_STAFF = "INSERT INTO qdlc_staff (name, gender, title, address, username, password, dateOfEmployment, status) VALUES (?,?,?,?,?,?,?,?);";
    private static final String UPDATE_STAFF = "UPDATE qdlc_staff SET name = ?, gender = ?, title = ? , address = ?, username = ?, password = ?, dateOfEmployment = ?, status = ? WHERE staffId = ?";
    private static final String GET_STAFF_FROM_ID = "SELECT * FROM qdlc_staff WHERE staffId = ?;";
    private static final String GET_ALL_STAFF = "SELECT * FROM qdlc_staff;";

    
    private SimpleDateFormat formatter = new SimpleDateFormat(
            "yyyy-MM-dd HH:mm:ss");
    
    
    public Staff createStaff(String name, String gender, String title, String address, String username, String password, Date dateOfEmployment, int status) {
        Staff staff = null;

        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        // Build query
        try {
            PreparedStatement st = conn.prepareStatement(CREATE_STAFF,
                    PreparedStatement.RETURN_GENERATED_KEYS);

            st.setString(1, name);
            st.setString(2, gender);
            st.setString(3, title);
            st.setString(4, address);
            st.setString(5, username);
            st.setString(6, password);
            st.setString(7, formatter.format(dateOfEmployment));
            st.setInt(8, status);
            
            
            int affectedRows = st.executeUpdate();

            // Don't attempt to get keys if there is no affected row
            if (affectedRows > 0) {
                ResultSet keys = st.getGeneratedKeys();

                // Get key, return newUser
                if (keys.next()) {
                    int staffId = keys.getInt(1);
                    
                    staff = new Staff();
                    
                    staff.setAddress(address);
                    staff.setGender(gender);
                    staff.setDateOfEmployment(dateOfEmployment);
                    staff.setName(name);
                    staff.setPassword(password);
                    staff.setStaffId(staffId);
                    staff.setStatus(status);
                    staff.setTitle(title);
                    staff.setUserName(username);
                    
                    if (keys != null) {
                        keys.close();
                    }
                }
            }
            
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return staff;
    }

    public void updateStaff(String name, String gender, String title, String address, String username, String password, Date dateOfEmployment, int status, int staffId) {
        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        try {
            PreparedStatement st = conn.prepareStatement(UPDATE_STAFF);
            st.setString(1, name);
            st.setString(2, gender);
            st.setString(3, title);
            st.setString(4, address);
            st.setString(5, username);
            st.setString(6, password);
            st.setString(7, formatter.format(dateOfEmployment));
            st.setInt(8, status);
            st.setInt(9, staffId);
            st.executeUpdate();

            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }
    }

    public Staff getStaff(int staffId) {
        Staff staff = null;
    
        // TODO: Add exceptions
        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        // Build query
        try {
            PreparedStatement st = conn.prepareStatement(GET_STAFF_FROM_ID);

            st.setInt(1, staffId);

            ResultSet rs = st.executeQuery();

            // Found result!
            if (rs.next()) {
               staff = new Staff();
               staff.setAddress(rs.getString("address"));
               staff.setDateOfEmployment(formatter.parse(rs.getString("dateOfEmployement")));
               staff.setGender(rs.getString("gender"));
               staff.setName(rs.getString("name"));
               staff.setPassword(rs.getString("password"));
               staff.setStaffId(rs.getInt("staffId"));
               staff.setTitle(rs.getString("title"));
               staff.setStatus(rs.getInt("status"));
               staff.setUserName(rs.getString("username"));
            }

            if (rs != null) {
                rs.close();
            }
            
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return staff;
    }
    
    public ArrayList<Staff> getAllConsultationRecord() {
        ArrayList<Staff> staffList = new ArrayList<Staff>();
        Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

        // Build query
        try {
            PreparedStatement st = conn.prepareStatement(GET_ALL_STAFF);

            ResultSet rs = st.executeQuery();

            // Found result!
            if (rs.next()) {
            	Staff staff = new Staff();
                staff.setAddress(rs.getString("address"));
                staff.setDateOfEmployment(formatter.parse(rs.getString("dateOfEmployement")));
                staff.setGender(rs.getString("gender"));
                staff.setName(rs.getString("name"));
                staff.setPassword(rs.getString("password"));
                staff.setStaffId(rs.getInt("staffId"));
                staff.setTitle(rs.getString("title"));
                staff.setStatus(rs.getInt("status"));
                staff.setUserName(rs.getString("username"));
                
                staffList.add(staff);
            }

            if (rs != null) {
                rs.close();
            }
            
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return staffList;
    }
    
}

